const XLSX = require('xlsx');
const mysql = require('mysql2/promise');

// 数据库连接配置
const db = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: '1234',
  database: 'word_memory'
});

// 读取 Excel 文件
const workbook = XLSX.readFile('./example_sentences_with_word.xlsx');
const sheet = workbook.Sheets[workbook.SheetNames[0]];
const data = XLSX.utils.sheet_to_json(sheet); // 默认从表头读取字段

async function main() {
  for (const row of data) {
    const { Word, Sentence, Translation } = row;
    if (!Word || !Sentence) continue;

    try {
      // 查找 Word 对应的 ID
      const [wordRows] = await db.query('SELECT id FROM words WHERE word = ?', [Word]);
      if (wordRows.length === 0) {
        console.warn(`❗️单词未找到，跳过：${Word}`);
        continue;
      }

      const wordId = wordRows[0].id;

      // 插入例句
      await db.query(
        'INSERT INTO word_sentences (word_id, sentence, translation) VALUES (?, ?, ?)',
        [wordId, Sentence, Translation || null]
      );

      console.log(`✅ 已插入：${Word}`);
    } catch (err) {
      console.error(`❌ 插入失败：${Word} - ${err.message}`);
    }
  }

  console.log('🎉 所有例句导入完成');
}

main();
